package com.yzycoc.hamster.demo.time202505.time20250517.test;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.DbUtil;
import cn.hutool.db.ds.simple.SimpleDataSource;
import com.yzycoc.hamster.demo.time202505.time20250517.info.DisasterPointInfo;
import com.yzycoc.hamster.demo.time202505.time20250517.info.CutSlopeBuildHouse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 创建时间： 2025/5/17
 *
 * @version 1.0
 **/
public class StarInfo {

    public static void main(String[] args) throws SQLException {
        // 1. 创建数据源
        SimpleDataSource dataSource = new SimpleDataSource(
                "jdbc:mysql://localhost:3306/xc_cloud?useSSL=false&serverTimezone=Asia/Shanghai",
                "root",
                "root"
        );
        Db db = DbUtil.use(dataSource);

        // 2. 获取数据库中的所有记录
        String sql = "SELECT * FROM cut_slope_build_house";
        List<CutSlopeBuildHouse> list = db.query(sql, CutSlopeBuildHouse.class);
        Map<String, CutSlopeBuildHouse> dbMap = list.stream()
                .filter(item -> StrUtil.isNotBlank(item.getUnitNumber()))
                .collect(Collectors.toMap(CutSlopeBuildHouse::getUnitNumber, Function.identity(), (a, b) -> a));

        // 3. 读取 Excel 数据
        List<DisasterPointInfo> excelList = readExcel(); // 你需要实现这个方法
        Map<String, DisasterPointInfo> excelMap = excelList.stream()
                .filter(item -> StrUtil.isNotBlank(item.getUnitNumber()))
                .collect(Collectors.toMap(DisasterPointInfo::getUnitNumber, Function.identity(), (a, b) -> a));

        List<String> excelIdList = readExcel2(); // 你需要实现这个方法

        List<String> ids = new ArrayList<>();
        for (String excelId : excelIdList) {
            if (StrUtil.isBlank(excelId)) {
                continue;
            }
            CutSlopeBuildHouse dbRecord = dbMap.get(excelId);
            if(dbRecord == null){
                return;
            }
            ids.add(dbRecord.getId() + "");
        }

        System.out.println(String.join(", ", ids));
        /*List<String> sqlList = new ArrayList<>();

        for (DisasterPointInfo info : excelList) {
            if (StrUtil.isBlank(info.getUnitNumber())) {
                continue;
            }
            //  34188100310241
            //  34188100410241
            String unitNumber = info.getUnitNumber().replace("34188100310241", "34188100410241");
            CutSlopeBuildHouse dbRecord = dbMap.get(unitNumber);

            if (dbRecord == null) {
                // 新增
                String insertSql = buildInsertSql(info);
                sqlList.add(insertSql);
            } else {
                // 修改
                String updateSql = buildUpdateSql(info, dbRecord);
                if (StrUtil.isNotBlank(updateSql)) {
                    sqlList.add(updateSql);
                }
            }
        }*/

        // 删除（数据库中存在但 Excel 中不存在的记录）
        //for (String unitNumber : dbMap.keySet()) {
        //    if (!excelMap.containsKey(unitNumber)) {
        //        sqlList.add("DELETE FROM cut_slope_build_house WHERE unit_number = '" + unitNumber + "';");
        //    }
        //}

        // 写入文件
        //File file = FileUtil.touch("static/sql_log" + System.currentTimeMillis() + ".sql");
        //FileUtil.writeUtf8Lines(sqlList, file);
        //System.out.println("SQL 文件已生成：" + file.getAbsolutePath());
    }


    private static String buildUpdateSql(DisasterPointInfo info, CutSlopeBuildHouse dbRecord) {
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE cut_slope_build_house SET ");
        List<String> updates = new ArrayList<>();
        updates.add("unit_number = '" + info.getUnitNumber() + "'");
        updates.add("own_name = '" + info.getOwnerName() + "'");
        updates.add("own_phone = '" + info.getPhone() + "'");
        updates.add("population = '" + info.getPopulation() + "'");
        updates.add("build_time = '" + info.getBuildYear() + "'");
        updates.add("house_type = '" + info.getHouseStructure() + "'");
        updates.add("house_location = '" + info.getHouseLocation() + "'");
        updates.add("base_form = '" + info.getBaseType() + "'");
        updates.add("number_of_floors = '" + info.getFloorCount() + "'");
        updates.add("number_of_layers = '" + info.getRoomCountPerFloor() + "'");
        updates.add("residential_location = '" + info.getLivePosition() + "'");
        updates.add("slope_toe_position = '" + info.getDistanceToSlope() + "'");

        updates.add("original_slope_shape = '" + info.getSlopeShape1() + "'");
        updates.add("original_slope_height = '" + info.getSlopeHeight1() + "'");
        updates.add("original_slope_long = '" + info.getSlopeLength1() + "'");
        updates.add("original_slope_width = '" + info.getSlopeWidth1() + "'");
        updates.add("original_slope = '" + info.getSlopeAngle1() + "'");
        updates.add("original_slope_direction = '" + info.getSlopeDirection1() + "'");

        updates.add("artificial_slope_shape = '" + info.getSlopeShape2() + "'");
        updates.add("artificial_slope_height = '" + info.getSlopeHeight2() + "'");
        updates.add("artificial_slope_long = '" + info.getSlopeLength2() + "'");
        updates.add("artificial_slope_width = '" + info.getSlopeWidth2() + "'");
        updates.add("artificial_slope = '" + info.getSlopeAngle2() + "'");
        updates.add("slope_direction = '" + info.getSlopeDirection2() + "'");

        updates.add("annual_rainfall = '" + info.getRainfallYearlyAvg() + "'");
        updates.add("q_max = '" + info.getRainfallMaxDaily() + "'");
        updates.add("hourly_maximum_rainfall = '" + info.getRainfallMaxHourly() + "'");

        updates.add("type_water = '" + info.getSurfaceWaterType() + "'");
        updates.add("relative_position_of_surface_water = '" + info.getRelativePositionToRiver() + "'");

        updates.add("land_use = '" + info.getLandUse() + "'");
        updates.add("veg_develop = '" + info.getVegetation() + "'");
        updates.add("topographic_features = '" + info.getTerrain() + "'");
        //updates.add(" = '" + info.getmicroGeomorphology() + "'");

        updates.add("land_character_name = '" + info.getSoilFeaturesName() + "'");
        updates.add("land_character_thickness = '" + info.getSoilFeaturesThickness() + "'");
        updates.add("land_character_density = '" + info.getSoilFeaturesSolidityRatio() + "'");

        updates.add("bedrock_era = '" + info.getBedrockFeaturesNd() + "'");
        updates.add("bedrock_lithology = '" + info.getBedrockFeaturesYx() + "'");
        updates.add("bedrock_occurrence = '" + info.getBedrockFeaturesCz() + "'");
        updates.add("bedrock_weather = '" + info.getBedrockFeaturesFh() + "'");

        updates.add("structural_plane_type = '" + info.getStructureFeaturesLx() + "'");
        updates.add("structural_plane_occurrence = '" + info.getStructureFeaturesCz() + "'");
        updates.add("structural_plane_long = '" + info.getStructureFeaturesCd() + "'");
        updates.add("structural_plane_space = '" + info.getStructureFeaturesJj() + "'");

        updates.add("water_type = '" + info.getGroundwaterType() + "'");
        updates.add("water_variation = '" + info.getGroundwaterLevelChange() + "'");
        updates.add("conditions = '" + info.getGroundwaterCondition() + "'");


        updates.add("deformation_sign_name = '" + info.getDeformationName() + "'");
        updates.add("deformation_sign_location = '" + info.getDeformationPart() + "'");
        updates.add("deformation_sign_character = '" + info.getDeformationFeature() + "'");
        updates.add("deformation_sign_date = '" + info.getDeformationTime() + "'");
        updates.add("instable_factor = '" + info.getKnx() + "'");
        updates.add("future_trends = '" + info.getJhb() + "'");
        updates.add("stability_analysis_results = '" + getWdx(info.getWdx()) + "'");


        updates.add("casualties = '" + info.getCasualties() + "'");
        updates.add("house_damage = '" + info.getHouseDamage() + "'");
        updates.add("road_damage = '" + info.getRoadDamage() + "'");
        updates.add("direct_loss = '" + info.getEconomicLoss() + "'");
        updates.add("disaster_level = '" + getdisaster_level(info.getDisasterLevel()) + "'");

        updates.add("threat_population = '" + info.getThreatPeople() + "'");
        updates.add("threat_house = '" + info.getThreatHouses() + "'");
        updates.add("threat_road = '" + info.getThreatRoadLength() + "'");
        updates.add("potential_losses = '" + info.getPotentialLoss() + "'");
        updates.add("dangerous_level = '" + getdangerousLevel(info.getDangerLevel()) + "'");

        updates.add("hazard_level = '" + gethazard_level(info.getCurrentHazard()) + "'");

        updates.add("measure = '" + info.getExistingMeasures() + "'");
        updates.add("disposal_measures = '" + info.getPreventionSuggestion() + "'");
        updates.add("on_off = '" + geton_off(info.getIncludedInManagement()) + "'");

        updates.add("village_director = '" + info.getVillageHead() + "'");
        updates.add("phone = '" + info.getVillagePhone() + "'");

        updates.add("survey_responsible = '" + info.getInvestigator() + "'");
        updates.add("survey_date = '" + info.getInvestigationDate() + "'");
        updates.add("survey_unit = '" + info.getInvestigationUnit() + "'");

        updates.add("status = '0'");
        updates.add("create_date = '2025-05-16 10:00:00'");

        sb.append(String.join(", ", updates));
        sb.append(" WHERE id = '").append(dbRecord.getId()).append("';");

        String select = "";
        for (String update : updates) {
            select += update.split("=")[0] + ",";
        }
        System.out.println("select " + select + " from cut_slope_build_house where id=" + dbRecord.getId());
        return sb.toString();
    }

    private static String geton_off(String includedInManagement) {
        switch (includedInManagement) {
            case "否":
                return "0";
            case "是":
                return "1";
            default:
                return "";
        }
    }

    private static String gethazard_level(String currentHazard) {
        switch (currentHazard) {
            case "小型":
                return "3";
            case "中型":
                return "2";
            case "大型":
                return "1";
            case "小":
                return "3";
            case "中":
                return "2";
            case "大":
                return "1";
            default:
                return "";
        }
    }


    private static String getdangerousLevel(String dangerLevel) {
        switch (dangerLevel) {
            case "小型":
                return "1";
            case "中型":
                return "2";
            case "大型":
                return "3";
            default:
                return ""; // 或返回 "0"，视业务需要
        }
    }


    private static String getdisaster_level(String disasterLevel) {
        switch (disasterLevel) {
            case "小型":
                return "1";
            case "中型":
                return "2";
            case "大型":
                return "3";
            case "特大型":
                return "4";
            default:
                return ""; // 或者 "0"，根据业务需求
        }
    }


    private static String buildInsertSql(DisasterPointInfo info) {
        return null;
    }

    public static String getWdx(String wdx) {
        switch (wdx) {
            case "稳定性好":
                return "1";
            case "稳定性较差":
                return "2";
            case "稳定性差":
                return "3";
            default:
                return ""; // 或者返回 "0"，视业务需要
        }
    }

    private static List<String> readExcel2() {
        List<String> result = new ArrayList<>();
        try {
            // 读取 classpath 下的文件
            ClassPathResource resource = new ClassPathResource("static/20250517/xuancheng.xlsx");
            InputStream inputStream = resource.getStream();

            // 创建工作簿
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(3); // 默认读取第一个 sheet

            // 遍历行（跳过表头，从第1行开始）
            for (int i = 3; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                String val = getCellStringValue(row.getCell(1));
                if(StrUtil.isEmpty(val)){
                    continue;
                }
                result.add(val.replace("34188100310241", "34188100410241"));
            }

            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    public static List<DisasterPointInfo> readExcel() {
        List<DisasterPointInfo> result = new ArrayList<>();
        try {
            // 读取 classpath 下的文件
            ClassPathResource resource = new ClassPathResource("static/20250517/xuancheng.xlsx");
            InputStream inputStream = resource.getStream();

            // 创建工作簿
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0); // 默认读取第一个 sheet

            // 遍历行（跳过表头，从第1行开始）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                DisasterPointInfo info = new DisasterPointInfo();
                int col = 0;

                info.setIndex(getCellStringValue(row.getCell(col++)));
                info.setUnitNumber(getCellStringValue(row.getCell(col++)));
                info.setCode(getCellStringValue(row.getCell(col++)));

                info.setProvince(getCellStringValue(row.getCell(col++)));
                info.setCity(getCellStringValue(row.getCell(col++)));
                info.setCounty(getCellStringValue(row.getCell(col++)));
                info.setTown(getCellStringValue(row.getCell(col++)));
                info.setVillage(getCellStringValue(row.getCell(col++)));
                info.setGroup(getCellStringValue(row.getCell(col++)));

                info.setLongitude(getCellStringValue(row.getCell(col++)));
                info.setLatitude(getCellStringValue(row.getCell(col++)));
                info.setCoordinateX(getCellStringValue(row.getCell(col++)));
                info.setCoordinateY(getCellStringValue(row.getCell(col++)));

                info.setOwnerName(getCellStringValue(row.getCell(col++)));
                info.setPhone(getCellStringValue(row.getCell(col++)));
                info.setPopulation(getCellIntegerValue(row.getCell(col++)));
                info.setBuildYear(getCellStringValue(row.getCell(col++)));
                info.setHouseStructure(getCellStringValue(row.getCell(col++)));
                info.setHouseLocation(getCellStringValue(row.getCell(col++)));
                info.setBaseType(getCellStringValue(row.getCell(col++)));
                info.setFloorCount(getCellIntegerValue(row.getCell(col++)));
                info.setRoomCountPerFloor(getCellStringValue(row.getCell(col++)));
                info.setLivePosition(getCellStringValue(row.getCell(col++)));
                info.setDistanceToSlope(getCellStringValue(row.getCell(col++)));

                // 原始切坡
                info.setSlopeShape1(getCellStringValue(row.getCell(col++)));
                info.setSlopeHeight1(getCellStringValue(row.getCell(col++)));
                info.setSlopeLength1(getCellStringValue(row.getCell(col++)));
                info.setSlopeWidth1(getCellStringValue(row.getCell(col++)));
                info.setSlopeAngle1(getCellStringValue(row.getCell(col++)));
                info.setSlopeDirection1(getCellStringValue(row.getCell(col++)));

                // 人工切坡
                info.setSlopeShape2(getCellStringValue(row.getCell(col++)));
                info.setSlopeHeight2(getCellStringValue(row.getCell(col++)));
                info.setSlopeLength2(getCellStringValue(row.getCell(col++)));
                info.setSlopeWidth2(getCellStringValue(row.getCell(col++)));
                info.setSlopeAngle2(getCellStringValue(row.getCell(col++)));
                info.setSlopeDirection2(getCellStringValue(row.getCell(col++)));

                info.setRainfallYearlyAvg(getCellStringValue(row.getCell(col++)));
                info.setRainfallMaxDaily(getCellStringValue(row.getCell(col++)));
                info.setRainfallMaxHourly(getCellStringValue(row.getCell(col++)));

                info.setSurfaceWaterType(getCellStringValue(row.getCell(col++)));
                info.setRelativePositionToRiver(getCellStringValue(row.getCell(col++)));

                info.setLandUse(getCellStringValue(row.getCell(col++)));
                info.setVegetation(getCellStringValue(row.getCell(col++)));
                info.setTerrain(getCellStringValue(row.getCell(col++)));
                info.setMicroGeomorphology(getCellStringValue(row.getCell(col++)));

                info.setSoilFeaturesName(getCellStringValue(row.getCell(col++)));
                info.setSoilFeaturesThickness(getCellStringValue(row.getCell(col++)));
                info.setSoilFeaturesSolidityRatio(getCellStringValue(row.getCell(col++)));


                info.setBedrockFeaturesNd(getCellStringValue(row.getCell(col++)));
                info.setBedrockFeaturesYx(getCellStringValue(row.getCell(col++)));
                info.setBedrockFeaturesCz(getCellStringValue(row.getCell(col++)));
                info.setBedrockFeaturesFh(getCellStringValue(row.getCell(col++)));


                info.setStructureFeaturesLx(getCellStringValue(row.getCell(col++)));
                info.setStructureFeaturesCz(getCellStringValue(row.getCell(col++)));
                info.setStructureFeaturesCd(getCellStringValue(row.getCell(col++)));
                info.setStructureFeaturesJj(getCellStringValue(row.getCell(col++)));


                info.setGroundwaterType(getCellStringValue(row.getCell(col++)));
                info.setGroundwaterLevelChange(getCellStringValue(row.getCell(col++)));
                info.setGroundwaterCondition(getCellStringValue(row.getCell(col++)));

                info.setDeformationName(getCellStringValue(row.getCell(col++)));
                info.setDeformationPart(getCellStringValue(row.getCell(col++)));
                info.setDeformationFeature(getCellStringValue(row.getCell(col++)));
                info.setDeformationTime(getCellStringValue(row.getCell(col++)));

                info.setKnx(getCellStringValue(row.getCell(col++)));

                info.setJhb(getCellStringValue(row.getCell(col++)));

                info.setWdx(getCellStringValue(row.getCell(col++)));


                info.setCasualties(getCellStringValue(row.getCell(col++)));
                info.setHouseDamage(getCellStringValue(row.getCell(col++)));
                info.setRoadDamage(getCellStringValue(row.getCell(col++)));
                info.setEconomicLoss(getCellStringValue(row.getCell(col++)));
                info.setDisasterLevel(getCellStringValue(row.getCell(col++)));

                info.setThreatPeople(getCellStringValue(row.getCell(col++)));
                info.setThreatHouses(getCellStringValue(row.getCell(col++)));
                info.setThreatRoadLength(getCellStringValue(row.getCell(col++)));
                info.setPotentialLoss(getCellStringValue(row.getCell(col++)));
                info.setDangerLevel(getCellStringValue(row.getCell(col++)));

                info.setCurrentHazard(getCellStringValue(row.getCell(col++)));

                info.setInFloodDatabase(getCellStringValue(row.getCell(col++)));
                info.setFloodDatabaseId(getCellStringValue(row.getCell(col++)));

                info.setExistingMeasures(getCellStringValue(row.getCell(col++)));
                info.setPreventionSuggestion(getCellStringValue(row.getCell(col++)));
                info.setIncludedInManagement(getCellStringValue(row.getCell(col++)));

                info.setVillageHead(getCellStringValue(row.getCell(col++)));
                info.setVillagePhone(getCellStringValue(row.getCell(col++)));

                info.setInvestigator(getCellStringValue(row.getCell(col++)));
                info.setInvestigationDate(getCellDateValue(row.getCell(col++)));
                info.setInvestigationUnit(getCellStringValue(row.getCell(col++)));

                result.add(info);
            }

            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    private static String getCellDateValue(Cell cell) {
        if (cell == null) return null;

        // Excel单元格是数字类型并且是日期格式
        if (cell.getCellType() == CellType.NUMERIC) {
            DateTime date = cn.hutool.core.date.DateUtil.date(cell.getDateCellValue());
            return cn.hutool.core.date.DateUtil.format(date, "yyyy-MM-dd HH:mm:ss");
        }

        // 如果是字符串，例如 "2019/8/20" 或 "2019-08-20"
        if (cell.getCellType() == CellType.STRING) {
            String text = cell.getStringCellValue().trim();
            try {
                DateTime date = cn.hutool.core.date.DateUtil.parse(text);
                return cn.hutool.core.date.DateUtil.format(date, "yyyy-MM-dd HH:mm:ss");
            } catch (Exception e) {
                return null;
            }
        }

        return null;
    }

    private static String getCellStringValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }

    private static Integer getCellIntegerValue(Cell cell) {
        if (cell == null) return null;
        try {
            return (int) cell.getNumericCellValue();
        } catch (Exception e) {
            try {
                return Integer.valueOf(cell.getStringCellValue().trim());
            } catch (Exception ex) {
                return null;
            }
        }
    }

}
